﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTEKVKVRData : BaseFunction
    {
        
        public MVTEKVKVRData()
            : base()
        {

        }
        public MVTEKVKVRData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetEKVKVR(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "EKVKVR";
            string PI = dwABPOS["PI"].ToString().Trim();
            string INVOICE = dwABPOS["INVOICE"].ToString().Trim();
            string KUNDNR = dwABPOS["KUNDNR"].ToString().Trim();
            string STATUS = dwABPOS["STATUS"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.EKPNEUD,D.VRPRENR,G.VVTLIEFSCH,C.VKPAUFKRS,C.VKPAUFLFD, "
                    + " C.VKPAUFPOS,A.EKPAUFPOS,C.VKPNEUBEN, "
                    + " C.VKPAUFKRS||'-'||TO_NUMBER(C.VKPAUFLFD)||'-'||TRIM(E.KDKUNDNA) AS PI,C.VKPWAEHRSL, "
                    + " E.KDKUNDNA,C.VKPART,C.VKPFARBE,C.VKPMENGE1*0.01 AS ORDQTY,C.VKPMEEH, "
                    + " ROUND(C.VKPMENGE1*0.01*c.vkpmeehfkt*0.000001,2) AS ORDMTR, "
                    + " C.VKPKURS*0.0001 AS RATE,C.VKPPREIS*0.001 AS SOPRICE, "
                    + " ROUND((C.VKPPREIS*0.001)/(C.vkpprehfkt*0.000001),2) as SOMTRPRICE, "
                    + " A.EKPPRME*0.001 AS POPRICE, "
                    + " ROUND((A.EKPPRME*0.001)/(A.EKPPREHFKT*0.000001),2) AS POMTRPRICE, "
                    + " round((A.EKPPRME*0.001)/(A.EKPPREHFKT*0.000001)/(C.VKPKURS*0.0001),2) AS QDMTRPRICE, "
                    + " nvl(G.VVQTY,0) AS DELQTY,D.INVQTY,D.VRKKOMPL, "
                    + " (EKPZUABWE1+EKPZUABWE2+EKPZUABWE3+EKPZUABWE4+EKPZUABWE5)*0.001 AS POSUR, "
                    + " NVL((VKZZUABWERT1+VKZZUABWERT2+VKZZUABWERT3+VKZZUABWERT4+VKZZUABWERT5)*0.01,0) AS SOSUR,D.COMMISSION,D.VEVERTNA, "
                    + " (CASE WHEN nvl(G.VVQTY,0)=0 THEN 0  "
                    + " ELSE round((ROUND((C.VKPPREIS*0.001)/(C.vkpprehfkt*0.000001),2) "
                    + "       -round((A.EKPPRME*0.001)/(A.EKPPREHFKT*0.000001)/(C.VKPKURS*0.0001),2)) "
                    + "       * NVL(G.VVQTY,0),2) "
                    + "       +(NVL((VKZZUABWERT1+VKZZUABWERT2+VKZZUABWERT3+VKZZUABWERT4+VKZZUABWERT5)*0.01,0)) "
                    + "      -ROUND(((EKPZUABWE1+EKPZUABWE2+EKPZUABWE3+EKPZUABWE4+EKPZUABWE5)*0.001) "
                    + "        /(C.VKPKURS*0.0001),2) END) AS PROFITS "
                    + " FROM EKPOS A,(SELECT DISTINCT ABAMANDANT,ABAAUFKRS,ABAAUFLFD,ABAAUFPOS, "
                    + " ABAVKAUFKRS,ABAVKAUFLFD,ABAVKAUFPOS  FROM ABAUFM "
                    + " WHERE ABAAUFTYP = 'B' AND ABAAPOS >'0001' AND ABAVKAUFTYP='K' ) B, "
                    + " VKPOS C,KDSTAMM E,VKZUAB F, "
                    + " (SELECT VVTMANDANT,VVTAUFKRS,VVTAUFLFD,VVTAUFPOS,VVTLIEFSCH, "
                    + " SUM(VVTMTR*0.01) AS VVQTY  "
                    + " FROM VVTLIEF GROUP BY VVTMANDANT,VVTAUFKRS,VVTAUFLFD,VVTAUFPOS,VVTLIEFSCH) G, "
                    + " (SELECT X.VRPMANDANT,X.VRPRENR,Y.VRKKOMPL,X.VRPAUFKRS,X.VRPAUFLFD, "
                    + " X.VRPAUFPOS,X.VRPMENGE * 0.01 AS INVQTY,Z.VEVERTNA, "
                    + " DECODE(X.VRPVERTNR1,'        ',0,(CASE WHEN X.VRPPROVP1=0 "
                    + " THEN X.VRPGESWERT*0.001-T.VA ELSE X.VRPGESWERT*0.001*X.VRPPROVP1*0.0001 END )) AS COMMISSION "
                    + " FROM VRPOS X, VRKOPF Y,VESTAMM Z, "
                    + " (SELECT A1.VRPAUFKRS,A1.VRPAUFLFD,A1.VRPAUFPOS,A1.VRPLIEFSCH, "
                    + " A1.VRPRRENR,A1.VRPRREPOS,A1.VRPGESWERT*0.001 AS VA,A1.VRPRENR "
                    + " FROM VRPOS A1,VRKOPF B1 WHERE A1.VRPMANDANT = B1.VRKMANDANT "
                    + " AND A1.VRPRENR = B1.VRKRENR AND B1.VRKREART = 'GS' AND A1.VRPSTORNO <>'J') T "
                    + " WHERE X.VRPMANDANT = Y.VRKMANDANT AND X.VRPRENR = Y.VRKRENR "
                    + " AND X.VRPMANDANT = Z.VEMANDANT(+) AND X.VRPVERTNR1 = Z.VEVERTNR(+) "
                    + " AND X.VRPRENR = T.VRPRRENR(+) AND X.VRPREPOS = T.VRPRREPOS(+)  "
                    + " AND X.VRPAUFKRS = T.VRPAUFKRS(+) AND X.VRPAUFLFD = T.VRPAUFLFD(+) "
                    + " AND X.VRPAUFPOS = T.VRPAUFPOS(+) AND X.VRPLIEFSCH = T.VRPLIEFSCH(+) "
                    + " AND X.VRPSTORNO <> 'J' AND Y.VRKREART = 'RE' ) D "
                    + " WHERE A.EKPSA = 'PO' AND A.EKPAUFART = 'BE' "
                    + " AND A.EKPMANDANT = B.ABAMANDANT AND A.EKPAUFKRS = B.ABAAUFKRS "
                    + " AND A.EKPAUFLFD = B.ABAAUFLFD AND A.EKPAUFPOS = B.ABAAUFPOS "
                    + " AND B.ABAMANDANT = C.VKPMANDANT AND B.ABAVKAUFKRS = C.VKPAUFKRS "
                    + " AND B.ABAVKAUFLFD = C.VKPAUFLFD AND B.ABAVKAUFPOS = C.VKPAUFPOS "
                    + " AND C.VKPSA <>'AJ' AND C.VKPMANDANT = E.KDMANDANT "
                    + " AND C.VKPKUNDNR = E.KDKUNDNR AND C.VKPMANDANT = D.VRPMANDANT(+) "
                    + " AND C.VKPAUFKRS = D.VRPAUFKRS(+) AND C.VKPAUFLFD = D.VRPAUFLFD(+) "
                    + " AND C.VKPAUFPOS = D.VRPAUFPOS(+) AND C.VKPMANDANT = F.VKZMANDANT(+) "
                    + " AND C.VKPAUFKRS = F.VKZAUFKRS(+) AND C.VKPAUFLFD = F.VKZAUFLFD(+) "
                    + " AND C.VKPAUFPOS = F.VKZAUFPOS(+) AND C.VKPSA = F.VKZSA(+) "
                    + " AND C.VKPMANDANT = G.VVTMANDANT(+) AND C.VKPAUFKRS = G.VVTAUFKRS(+) "
                    + " AND C.VKPAUFLFD = G.VVTAUFLFD(+) AND C.VKPAUFPOS = G.VVTAUFPOS(+) "
                    + " AND C.VKPVB='USD' "
                    + " AND (A.EKPNEUD >= " + FROMDATE + " AND  A.EKPNEUD <= " + ENDDATE + ") "
                    + " AND (trim(E.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " AND (C.VKPAUFKRS||'-'||TO_NUMBER(C.VKPAUFLFD)||'-'||TRIM(E.KDKUNDNA) = '" + PI + "' OR '" + PI + "' = 'AAA' ) "
                    + " AND (D.VRKKOMPL = '" + STATUS + "' OR '" + STATUS + "' = 'AAA' ) "
                    + " AND (TO_NUMBER(D.VRPRENR) = '" + INVOICE + "' OR '" + INVOICE + "' = 'AAA' ) "
                    + " ORDER BY 1 "
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    /*byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSFB"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSFB"] = value;
                    }*/
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTEKVKVRData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetEKVKVR(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}